package com.neu.questionnaire_system.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.neu.questionnaire_system.entity.Tenant;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author xingle
 * @since 2022-10-31
 */
@Mapper
public interface TenantMapper extends BaseMapper<Tenant> {

    @Insert("insert into tenant values (#{tenantName},#{tid},#{id},#{fee},#{password},#{phone},#{isDeleted})")
    int insertTenant(@Param("tenantName")String tenantName, @Param("tid")String tid, @Param("id")String id,
                     @Param("fee")Double fee, @Param("password")String password, @Param("phone")String phone,@Param("isDeleted")String isDeleted);

    @Select("select * from tenant where isDeleted = '0'")
    IPage<Tenant> showTenant(Page page);

    @Update("update tenant set isDeleted='1' where tid=#{tid}")
    int deleteTenant(@Param("tid")String tid);

    @Select("select * from tenant where tid=#{tid}")
    List<Tenant> findTenantById(@Param("tid")String tid);

    @Update("update tenant set tenantName=#{tenantName},password=#{password},phone=#{phone} where tid=#{tid}")
    int updateTenantById(@Param("tid")String tid,@Param("tenantName")String tenantName,
                         @Param("password")String password, @Param("phone")String phone);

    @Select("select * from tenant where tenantName like #{tenantName} and isDeleted='0'")
    List<Tenant> findTenantByName(@Param("tenantName")String tenantName);

    /**
     * created by libo 根据租户名字查询租户的tid
     * @param tenantName 租户名字
     * @return 返回租户的tid
     */
    @Select("select tid from tenant where tenantName = #{tenantName} and isDeleted!='0'")
    String findTidByName(@Param("tenantName")String tenantName);

    /**
     * created by libo 逻辑删除租户
     * @param tid 被删除租户的tid
     * @return 返回结果
     */
    @Update("update tenant set isDeleted='1' where tid=#{tid}")
    int deleteTenantByTrue(@Param("tid")String tid);

    @Update("update tenant set isDeleted='1' where tenantName=#{tenantName}")
    int deleteTenantByName(@Param("tenantName")String tenantName);

    @Update("update tenant set password=#{password},phone=#{phone},tenantName=#{newTenantName}where tenantName=#{oldTenantName}")
    int updateTenantByName(@Param("oldTenantName")String oldTenantName,@Param("newTenantName")String newTenantName,
                         @Param("password")String password, @Param("phone")String phone);

    //@Select("select uid from user where tid = #{tid,jdbcType=VARCHAR}")
    //List<String> ClusterFee(@Param("tid")String tid);

    @Update("update tenant set fee=#{fee} where tid=#{tid}")
    int updateFeeById(@Param("fee")double fee,@Param("tid")String tid);



    @Select("select distinct count(cid) FROM cluster as c left join user as u on c.uid=u.uid  WHERE u.tid = #{tid,jdbcType=VARCHAR}")
    int ClusterFee(@Param("tid")String tid);

    @Select("select distinct count(qid) from questionnaire as q left join user as u on q.uid=u.uid where u.tid = #{tid,jdbcType=VARCHAR}")
    int QuestionnaireFee(@Param("tid")String tid);

    @Select("select q.answeredCount from questionnaire as q left join user as u on q.uid=u.uid where u.tid = #{tid,jdbcType=VARCHAR}")
    List<Integer> AnswerFee(@Param("tid")String tid);

    @Select("select uid from user where tid=#{tid}")
    List<String>uidInTid(@Param("tid")String tid);


    @Select("select t.tenantName,t.phone,t.fee,\n" +
            "Count(t1.uid) uCount,\n" +
            "Sum(if(t1.qcount!=0,t1.qcount,0)) qCount,\n" +
            "Sum(if(t1.ccount!=0,t1.ccount,0)) cCount\n" +
            "from tenant t\n" +
            "left join\n" +
            "(\n" +
            "\tselect u.uid,Count(q.qid) qcount,Count(c.cid) ccount,u.tid from user u\n" +
            "\tLEFT JOIN questionnaire q\n" +
            "\ton u.uid = q.uid\n" +
            "\tleft join cluster c\n" +
            "\ton u.uid = c.uid\n" +
            "\tgroup by u.uid\n" +
            ") t1\n" +
            "on t1.tid = t.tid\n" +
            "group by t1.tid")
    List<Map<String,Object>>tenantStatistic();

    @Select("select tid from tenant where isDeleted='0'")
    List<String> findAllTid();



}
